package com.tfblog.blog.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.tfblog.blog.entity.Blog;
import com.tfblog.blog.entity.Tag;
import com.tfblog.blog.vo.DayCount;
import com.tfblog.blog.vo.TimeVO;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;

import java.util.Date;
import java.util.List;
import java.util.Map;


/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author tfeng
 * @since 2021-04-26
 */
public interface BlogMapper extends BaseMapper<Blog> {

    //根据标签的id进行联表分页查询(逻辑删除 = 0, 是发布的博客,不是草稿)  : 不带key搜索条件
    @Select("SELECT * from tf_blog b where b.deleted = 0 AND b.published = 1 AND b.id in (SELECT bt.blogs_id FROM tf_blog_tags bt WHERE bt.tags_id = #{id})")
    List<Blog> queryPageByTag(Page<Blog> pagination,Long id);

    //根据标签的id进行联表分页查询(逻辑删除 = 0, 是发布的博客,不是草稿)  : 带key搜索条件
    @Select("SELECT * from tf_blog b where b.deleted = 0 AND b.published = 1 AND b.title like #{key} AND  b.id in (SELECT bt.blogs_id FROM tf_blog_tags bt WHERE bt.tags_id = #{id})")
    List<Blog> queryPageByTagSearch(Page<Blog> pagination,String key,Long id);

    //根据标签的id进行联表查询所有的博客
    @Select("SELECT * from tf_blog b where b.deleted = 0 AND b.published = 1 AND b.id in (SELECT bt.blogs_id FROM tf_blog_tags bt WHERE bt.tags_id = #{id})")
    List<Blog> queryPageByTagId(Long id);

    //tf_blog_tags的插入:注意,插入返回值为插入的主键值
    @Insert("INSERT tf_blog_tags (blogs_id,tags_id) VALUES(#{blogId},#{tagId})")
    Long insertBlogAndTagId(Long blogId,Long tagId);

    //根据博客id和标签id查询对应的博客内容(未使用)
    @Select("SELECT * FROM tf_blog b,tf_blog_tags bt WHERE b.deleted = 0 AND b.published = 1 AND b.id = #{blogId} AND bt.tags_id = #{tagId} AND b.id = bt.blogs_id")
    Blog selectBlogByTagId(Long blogId,Long tagId);

    //根据博客id查找对应的标签信息
    @Select("SELECT t.id,t.name from tf_tag t WHERE t.id in (SELECT bt.tags_id FROM tf_blog_tags bt WHERE bt.blogs_id = #{id})")
    List<Tag> selectTagsByBlogId(Long id);

    //删除指定博客的博客--标签的外键映射表关系
    @Delete("DELETE FROM tf_blog_tags WHERE blogs_id = #{id}")
    Long deleteBlogAndTags(Long id);

    //查询指定年月的博客信息
    @Select("SELECT id,create_time,title FROM tf_blog WHERE deleted = 0 AND published = 1 AND YEAR(create_time) = #{year} AND MONTH(create_time) = #{month}")
    List<TimeVO> selectTimeInfo(Long year, Long month);

    //查询指定分类的博客数量
    @Select("SELECT COUNT(*) FROM tf_blog WHERE deleted = 0 AND published = 1 AND type_id = #{id}")
    Integer selectBlogCountByTypeId(Long id);

    //查询指定标签的博客数量
    @Select("SELECT COUNT(*) FROM tf_blog b WHERE b.deleted = 0 AND b.published = 1 AND b.id in (SELECT bt.blogs_id FROM tf_blog_tags bt WHERE bt.tags_id = #{id})")
    Integer selectBlogCountByTagId(Long id);

    //查询指定年份中有博客的日期和对应的博客数量
    @Select("SELECT t.time AS time,COUNT(t.time) AS number FROM (SELECT DATE_FORMAT(temp.create_time,'%Y-%m-%d') TIME FROM (SELECT * FROM tf_blog b WHERE b.deleted = 0 AND b.published = 1 AND YEAR(b.create_time) = #{year}) AS temp) AS t GROUP BY t.time")
    List<DayCount> selectEveryDayBlogCount(Long year);

    //查询最小的年份
    @Select("SELECT YEAR(MIN(create_time)) FROM tf_blog WHERE published = 1 AND deleted = 0")
    Long selectMinYear();
}
